<?php

/* Total of hardware by:
    - Category
    - Condition
  (Warehouse, Floor or CPU) 
*/
$data = array( 
  'cat_cond' => '
    SELECT COUNT( * ) AS total
    FROM `hardware` AS hw
    LEFT JOIN (
      models AS mdl, categories AS cat
    ) ON ( model_id = mdl.id
    AND category_id = cat.id )
    WHERE category_id = %s
    AND condition_id = %s'
  );

/* Total of hardware by:
    - Category
    - Conditiong
    - Building
  outcase (Floor + Warehouse)
*/
$data['outcase_bldg'] = '
  SELECT (
    (
    SELECT COUNT( * )
    FROM `hardware` AS hw1
    LEFT JOIN (
      models AS mdl1, categories AS cat1, locations AS loc, departments AS dpt
    ) ON ( model_id = mdl1.id
    AND category_id = cat1.id
    AND location_id = loc.id
    AND department_id = dpt.id )
    WHERE category_id =%1$s
    AND condition_id =%2$s
    AND building_id =%3$s
    AND location_id IS NOT NULL
    ) + (
    SELECT COUNT( * )
    FROM `hardware` AS hw2
    LEFT JOIN (
    models AS mdl2, categories AS cat2, warehouse AS wh
    ) ON ( model_id = mdl2.id
    AND category_id = cat2.id
    AND warehouse_id = wh.id )
    WHERE category_id =%1$s
    AND condition_id =%2$s
    AND building_id =%3$s
    AND warehouse_id IS NOT NULL )
  ) AS total)';

/* Total of hardware by:
    - Category
    - Conditiong
    - Building
  incase (Floor + Warehouse)
*/
$data['incase_bldg'] = '
  SELECT (
    (
    SELECT COUNT( * )
    FROM `hardware` AS hw1
    LEFT JOIN (
      models AS mdl1, categories AS cat1, cpus AS cp1, locations AS loc, departments AS dpt
    ) ON ( model_id = mdl1.id
    AND category_id = cat1.id
    AND cpu_id = cp1.id
    AND cpu_location_id = loc.id
    AND department_id = dpt.id )
    WHERE category_id =%1$s
    AND condition_id =%2$s
    AND building_id =%3$s
    AND cpu_id IS NOT NULL
    AND cpu_location_id IS NOT NULL
    ) + (
    SELECT COUNT( * )
    FROM `hardware` AS hw2
    LEFT JOIN (
      models AS mdl2, categories AS cat2, cpus AS cp2, warehouse AS wh
    ) ON ( model_id = mdl2.id
    AND category_id = cat2.id
    AND cpu_warehouse_id = wh.id )
    WHERE category_id =%1$s
    AND condition_id =%2$s
    AND building_id =%3$s
    AND cpu_id IS NOT NULL
    AND cpu_warehouse_id IS NOT NULL )
  ) AS total)';

?>